package com.ruoyi.dataserver.line.utils;

import com.ruoyi.dataserver.line.dto.LineFileAndOperationSymbolAndValueDto;
import com.ruoyi.dataserver.line.enums.LineFieldEnum;
import com.ruoyi.dataserver.line.enums.OperationSymbolEnum;

import java.util.List;

import static com.ruoyi.dataserver.line.enums.LineFieldEnum.*;
import static com.ruoyi.dataserver.line.enums.OperationSymbolEnum.*;

/**
 * @ProjectName intelligent-city
 * @Package com.lab.dataserver.line.utils
 * @ClassName LineSqlUtil
 * @Description 管线sql生成工具类
 * @Company lab
 * @Author zgq
 * @Date 2020/7/17 11:19
 * @UpdateUser
 * @UpdateDate
 * @UpdateRemark
 * @Version 1.0.0
 */
public class LineSqlUtil {

    /**
     * 根据表名和where条件生成countSQL
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @return
     */
    public static String countSql(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = "SELECT COUNT(1) FROM \"" + tableName +"\"" + whereSql(lovList);
        return sql;
    }

    /**
     * 根据表名和where条件以及分页条件生成分页查询数据Sql
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @param pageUtil  分页工具类
     * @return
     */
    public static String pageSelectSql(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList, PageUtil pageUtil) {
        //String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT * FROM " + tableName + whereSql(lovList) + " ) TMP WHERE ROWNUM <= " + getEnd(pageUtil) + " ) x WHERE ROW_ID > " + getBegin(pageUtil);
        //String sql = "SELECT *,@rownum:=@rownum+1 AS ROW_ID FROM " + tableName+",(SELECT @rownum:="+pageUtil.getSize()+") r" + whereSql(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        String sql = "SELECT * FROM \"" + tableName +"\"" +  whereSql(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        return sql;
    }

    /**
     * 根据where条件拼接where条件的SQl
     *
     * @param lovList
     * @return
     */
    private static String whereSql(List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = " WHERE 1 = 1";
        for (LineFileAndOperationSymbolAndValueDto lov : lovList) {
            LineFieldEnum lineField = getLineFieldEnumWithCode(lov.getLineField());
            OperationSymbolEnum operationSymbol = getOperationSymbolEnumWithCode(lov.getOperationSymbol());
            String value = lov.getValue();
            value = checkValue(value);
            if (LIKE == operationSymbol && lineField != US_ID) {
                value = "%" + value + "%";
            }
            switch (lineField) {
                case BSM:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case DM:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case QDDH:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case ZDDH:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case QDGC:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case ZDGC:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case QDMS:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case ZDMS:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case MSLX:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case CZ:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case GJ:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + getGjNumber(lineField.getFieldName()) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case DYZ:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), dyzReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case YL:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case LX:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case ZKS:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case ZYKS:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case DLTS:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case BHCZ:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case XX:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case GXFLDM:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case SZDL:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case QSDW:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case JSND:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toNumner(regexpSubstr(lineField.getFieldName(), spaceReg, 1)) + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case TCDW:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case TCRQ:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + toDate(lineField.getFieldName()) + " " + operationSymbol.getOperationSymbol() + " " + toDate("'" + value + "'");
                    break;
                case SYZT:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case BZ:
                    sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                    break;
                case US_ID:
                    /**
                     * EQ是管线分页查询, LIKE是关联查询(关联查询,通过附属物查询管线)
                     */
                    switch (operationSymbol) {
                        case EQ:
                            sql += " AND " + lineField.getFieldName() + " " + operationSymbol.getOperationSymbol() + " '" + value + "'";
                            break;
                        case LIKE:
                            sql += " AND (" + QDDH.getFieldName() + " " + EQ.getOperationSymbol() + " '" + value + "' OR " + ZDDH.getFieldName() + " " + EQ.getOperationSymbol() + " '" + value + "')";
                            break;
                    }
                    break;
            }
        }
        return sql;
    }

    /**
     * 根据分页条件计算得出分页起始位置
     *
     * @param pageUtil
     * @return
     */
    public static Long getBegin(PageUtil pageUtil) {
        return (pageUtil.getCurrent() - 1) * pageUtil.getSize();
    }

    /**
     * 根据分页条件计算得出分页结束位置
     *
     * @param pageUtil
     * @return
     */
    public static Long getEnd(PageUtil pageUtil) {
        return pageUtil.getCurrent() * pageUtil.getSize();
    }

    /**
     * 转换为数字的sql
     *
     * @param str
     * @return
     */
    private static String toNumner(String str) {
        String sql = "cast(" + str + " as varchar)";
        return sql;
    }

    /**
     * 转换为日期的sql
     *
     * @param str
     * @return
     */
    private static String toDate(String str) {
        String sql = "TO_DATE(" + str + ",'yyyy/MM/dd')";
        return sql;
    }

    private static String gjReg = "X";
    private static String dyzReg = "kV";
    private static String spaceReg = " ";

    /**
     * 为管径和电压值截取字段
     *
     * @param filedName 字段
     * @param reg       截取字符
     * @param index     下坐标
     * @return
     */
    private static String regexpSubstr(String filedName, String reg, int index) {
        String sql = "REGEXP_SUBSTR(" + filedName + ",'[^" + reg + "]+',1," + index + ",'i')";
        return sql;
    }

    /**
     * 获取管径最大值(number格式的)
     *
     * @param filedName
     * @return
     */
    private static String getGjNumber(String filedName) {
        String sql = "CASE WHEN " + toNumner(regexpSubstr(filedName, gjReg, 1)) + " >= "
                + toNumner("CASE WHEN " + regexpSubstr(filedName, gjReg, 2) + " IS NULL THEN " + regexpSubstr(filedName, gjReg, 1) + " ELSE " + regexpSubstr(filedName, gjReg, 2) + " END")
                + " THEN " + toNumner(regexpSubstr(filedName, gjReg, 1))
                + " ELSE "
                + toNumner(regexpSubstr(filedName, gjReg, 2))
                + " END";
        return sql;
    }

    /**
     * 转成小写
     *
     * @param str 字段名称或者值(如果是值,记得带'')
     * @return
     */
    private static String lower(String str) {
        String sql = "LOWER(" + str + ")";
        return sql;
    }

    /**
     * 单引号' 转成 ''
     *
     * @param value
     * @return
     */
    private static String checkValue(String value) {
        value = value.replace("'", "''");
        return value;
    }

    /**
     * 特殊字符转义 / % _(借助escape)
     *
     * @param value
     * @return
     */
    private static String checkValue2(String value) {
        value = value.replace("\\", "\\\\");
        value = value.replace("%", "\\%");
        value = value.replace("_", "\\_");
        return value;
    }

    /*********************关键字查询start***********************/

    /**
     * 根据表名和where条件生成countSQL(关键字查询)
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @return
     */
    public static String countSql2(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = "SELECT COUNT(1) FROM \"" + tableName +"\"" + whereSql2(lovList);
        return sql;
    }

    /**
     * 根据表名和where条件以及分页条件生成分页查询数据Sql(关键字查询)
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @param pageUtil  分页工具类
     * @return
     */
    public static String pageSelectSql2(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList, PageUtil pageUtil) {
        //String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT * FROM " + tableName + whereSql2(lovList) + " ) TMP WHERE ROWNUM <= " + getEnd(pageUtil) + " ) x WHERE ROW_ID > " + getBegin(pageUtil);
        //String sql = "SELECT *,@rownum:=@rownum+1 AS ROW_ID FROM " + tableName+",(SELECT @rownum:="+pageUtil.getSize()+") r" + whereSql2(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        String sql = "SELECT * FROM \"" + tableName +"\"" +  whereSql2(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        return sql;
    }

    /**
     * 根据where条件拼接where条件的SQl(关键字查询)
     *
     * @param lovList
     * @return
     */
    private static String whereSql2(List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = " WHERE 1 = 1";
        for (LineFileAndOperationSymbolAndValueDto lov : lovList) {
            LineFieldEnum lineField = getLineFieldEnumWithCode(lov.getLineField());
            String value = lov.getValue();
            value = checkValue(value);
            switch (getOperationSymbolEnumWithCode(lov.getOperationSymbol())) {
                case EQ:
                    sql += " AND " + lineField.getFieldName() + " " + EQ.getOperationSymbol() + " '" + value + "'";
                    break;
                case LIKE:
                    value = value.toLowerCase();
                    value = checkValue2(value);
                    sql += " AND " + lower(lineField.getFieldName()) + " " + LIKE.getOperationSymbol() + " '%" + value + "%' ";
                    break;
            }
        }
        return sql;
    }

    /*********************关键字查询end***********************/

    /*********************管径查询start***********************/

    /**
     * 根据表名和where条件生成countSQL
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @return
     */
    public static String countSql3(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = "SELECT COUNT(1) FROM \"" + tableName +"\""+ whereSql3(lovList);
        return sql;
    }

    /**
     * 根据表名和where条件以及分页条件生成分页查询数据Sql
     *
     * @param tableName 表名
     * @param lovList   where条件集合
     * @param pageUtil  分页工具类
     * @return
     */
    public static String pageSelectSql3(String tableName, List<LineFileAndOperationSymbolAndValueDto> lovList, PageUtil pageUtil) {
        //String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT * FROM " + tableName + whereSql3(lovList) + " ) TMP WHERE ROWNUM <= " + pageUtil.getTotal() + " ) x WHERE ROW_ID > " + 0;
       // String sql = "SELECT *,@rownum:=@rownum+1 AS ROW_ID FROM " + tableName+",(SELECT @rownum:="+pageUtil.getSize()+") r" + whereSql3(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        String sql = "SELECT * FROM \"" + tableName +"\"" +  whereSql3(lovList) + " LIMIT "+getBegin(pageUtil)+","+pageUtil.getSize();
        return sql;
    }

    /**
     * 根据where条件拼接where条件的SQl
     *
     * @param lovList
     * @return
     */
    private static String whereSql3(List<LineFileAndOperationSymbolAndValueDto> lovList) {
        String sql = " WHERE 1 = 1";
        for (LineFileAndOperationSymbolAndValueDto lov : lovList) {
            LineFieldEnum lineField = getLineFieldEnumWithCode(lov.getLineField());
            OperationSymbolEnum operationSymbol = getOperationSymbolEnumWithCode(lov.getOperationSymbol());
            String value = lov.getValue();
            value = checkValue(value);
            switch (lineField) {
                case GJ:
                    sql += " AND " + lineField.getFieldName() + " IS NOT NULL";
                    sql += " AND " + lineField.getFieldName() + " != ' '";
                    sql += " AND " + getGjNumber(lineField.getFieldName()) + " " + operationSymbol.getOperationSymbol() + " " + value + "";
                    break;
            }
        }
        return sql;
    }

    /*********************管径查询end***********************/
}
